
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/16/2011 14:27:02
-- Generated from EDMX file: E:\workspace\CourseManager\CourseManager\Model\Data\DataModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [CMDB2];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Tab_Course]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tab_Course];
GO
IF OBJECT_ID(N'[dbo].[Tab_Log]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tab_Log];
GO
IF OBJECT_ID(N'[dbo].[Tab_News]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tab_News];
GO
IF OBJECT_ID(N'[dbo].[Tab_SC]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tab_SC];
GO
IF OBJECT_ID(N'[dbo].[Tab_Student]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tab_Student];
GO
IF OBJECT_ID(N'[dbo].[Tab_Teacher]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tab_Teacher];
GO
IF OBJECT_ID(N'[dbo].[Tab_Term]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tab_Term];
GO
IF OBJECT_ID(N'[dbo].[Tab_User]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tab_User];
GO
IF OBJECT_ID(N'[CMDB2ModelStoreContainer].[View_Course]', 'U') IS NOT NULL
    DROP TABLE [CMDB2ModelStoreContainer].[View_Course];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Tab_Course'
CREATE TABLE [dbo].[Tab_Course] (
    [courseId] varchar(10)  NOT NULL,
    [tId] varchar(10)  NOT NULL,
    [teacherId] varchar(4)  NOT NULL,
    [courseName] varchar(80)  NOT NULL,
    [credit] float  NOT NULL,
    [time] varchar(60)  NULL,
    [campus] varchar(10)  NULL,
    [place] varchar(40)  NULL,
    [dept] varchar(15)  NULL,
    [capacity] int  NULL
);
GO

-- Creating table 'Tab_Log'
CREATE TABLE [dbo].[Tab_Log] (
    [logId] int IDENTITY(1,1) NOT NULL,
    [userId] varchar(10)  NOT NULL,
    [ip] varchar(16)  NULL,
    [time] datetime  NULL,
    [action] varchar(50)  NULL
);
GO

-- Creating table 'Tab_News'
CREATE TABLE [dbo].[Tab_News] (
    [newsId] int IDENTITY(1,1) NOT NULL,
    [title] varchar(50)  NOT NULL,
    [content] varchar(1000)  NULL,
    [priority] int  NOT NULL,
    [time] datetime  NOT NULL
);
GO

-- Creating table 'Tab_SC'
CREATE TABLE [dbo].[Tab_SC] (
    [courseId] varchar(10)  NOT NULL,
    [studentId] varchar(10)  NOT NULL,
    [tId] varchar(4)  NOT NULL,
    [teacherId] varchar(10)  NULL,
    [termId] varchar(5)  NOT NULL,
    [grade] float  NOT NULL,
    [dayGrade] float  NOT NULL,
    [examGrade] float  NOT NULL
);
GO

-- Creating table 'Tab_Student'
CREATE TABLE [dbo].[Tab_Student] (
    [studentId] varchar(10)  NOT NULL,
    [name] varchar(10)  NOT NULL,
    [grade] int  NOT NULL,
    [sex] varchar(2)  NULL,
    [dept] varchar(15)  NULL
);
GO

-- Creating table 'Tab_Teacher'
CREATE TABLE [dbo].[Tab_Teacher] (
    [teacherId] varchar(10)  NOT NULL,
    [name] varchar(10)  NULL,
    [sex] varchar(2)  NULL,
    [dept] varchar(15)  NULL
);
GO

-- Creating table 'Tab_Term'
CREATE TABLE [dbo].[Tab_Term] (
    [termId] varchar(5)  NOT NULL,
    [state] int  NOT NULL
);
GO

-- Creating table 'Tab_User'
CREATE TABLE [dbo].[Tab_User] (
    [userId] varchar(10)  NOT NULL,
    [password] varchar(32)  NOT NULL,
    [type] int  NOT NULL
);
GO

-- Creating table 'View_Course'
CREATE TABLE [dbo].[View_Course] (
    [courseId] varchar(10)  NOT NULL,
    [tId] varchar(10)  NOT NULL,
    [name] varchar(80)  NOT NULL,
    [teacherName] varchar(10)  NULL,
    [credit] float  NOT NULL,
    [time] varchar(60)  NULL,
    [campus] varchar(10)  NULL,
    [place] varchar(40)  NULL,
    [capacity] int  NULL,
    [dept] varchar(15)  NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [courseId], [tId] in table 'Tab_Course'
ALTER TABLE [dbo].[Tab_Course]
ADD CONSTRAINT [PK_Tab_Course]
    PRIMARY KEY CLUSTERED ([courseId], [tId] ASC);
GO

-- Creating primary key on [logId] in table 'Tab_Log'
ALTER TABLE [dbo].[Tab_Log]
ADD CONSTRAINT [PK_Tab_Log]
    PRIMARY KEY CLUSTERED ([logId] ASC);
GO

-- Creating primary key on [newsId] in table 'Tab_News'
ALTER TABLE [dbo].[Tab_News]
ADD CONSTRAINT [PK_Tab_News]
    PRIMARY KEY CLUSTERED ([newsId] ASC);
GO

-- Creating primary key on [courseId], [studentId], [tId], [termId] in table 'Tab_SC'
ALTER TABLE [dbo].[Tab_SC]
ADD CONSTRAINT [PK_Tab_SC]
    PRIMARY KEY CLUSTERED ([courseId], [studentId], [tId], [termId] ASC);
GO

-- Creating primary key on [studentId] in table 'Tab_Student'
ALTER TABLE [dbo].[Tab_Student]
ADD CONSTRAINT [PK_Tab_Student]
    PRIMARY KEY CLUSTERED ([studentId] ASC);
GO

-- Creating primary key on [teacherId] in table 'Tab_Teacher'
ALTER TABLE [dbo].[Tab_Teacher]
ADD CONSTRAINT [PK_Tab_Teacher]
    PRIMARY KEY CLUSTERED ([teacherId] ASC);
GO

-- Creating primary key on [termId] in table 'Tab_Term'
ALTER TABLE [dbo].[Tab_Term]
ADD CONSTRAINT [PK_Tab_Term]
    PRIMARY KEY CLUSTERED ([termId] ASC);
GO

-- Creating primary key on [userId] in table 'Tab_User'
ALTER TABLE [dbo].[Tab_User]
ADD CONSTRAINT [PK_Tab_User]
    PRIMARY KEY CLUSTERED ([userId] ASC);
GO

-- Creating primary key on [courseId], [tId], [name], [credit] in table 'View_Course'
ALTER TABLE [dbo].[View_Course]
ADD CONSTRAINT [PK_View_Course]
    PRIMARY KEY CLUSTERED ([courseId], [tId], [name], [credit] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------